package com.returnsoft.softphone.eao;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.returnsoft.softphone.dto.AgentDto;
import com.returnsoft.softphone.dto.CampaignDto;
import com.returnsoft.softphone.dto.SessionDto;
import com.returnsoft.softphone.dto.SessionTypeDto;
import com.returnsoft.softphone.dto.SupervisorDto;
import com.returnsoft.softphone.entity.Agent;
import com.returnsoft.softphone.entity.Campaign;
import com.returnsoft.softphone.entity.Session;
import com.returnsoft.softphone.entity.SessionType;
import com.returnsoft.softphone.exception.EaoExcepcion;

@LocalBean
@Stateless
public class SessionEao implements Serializable {

	private static final long serialVersionUID = 1L;

	@PersistenceContext(name = "CCenterEJB", unitName = "CCenterEJB")
	private EntityManager em;

	public Session createSession(Session session) throws EaoExcepcion {
		try {
			// System.out.println("ingreso a createsession");
			if (session.getAgent() == null) {
				System.out.println("es nulo");
			} else {
				System.out.println("no es nulo");
				System.out.println(session.getAgent().getId());
			}
			session = (Session) em.merge(session);
			em.flush();
			return session;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	@SuppressWarnings("unchecked")
	public List<Session> findSessionsForAgent(Date startedAt, Integer agentId)
			throws EaoExcepcion {
		try {

			System.out.println("ingreso a findOpenSession");
			System.out.println(agentId);
			System.out.println(startedAt);
			System.out.println("ssss!!");

			String query =

			"SELECT s.id,s.startedAt,s.endedAt, " + "st.id,st.code,st.name,"
					+ "c.id,c.code,c.name," + "a.id,a.code "
					+ "FROM Session s " + "left join s.agent a "
					+ "left join s.campaign c " + "left join s.sessionType st "
					+ "where a.id = :agentId " + "and st.code != 'session' "
					+ "and s.startedAt like :startedAt ";

			Query q = em.createQuery(query);

			q.setParameter("agentId", agentId);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			q.setParameter("startedAt", sdf.format(startedAt) + "%");

			System.out.println("antes de ejecutar el query");

			List<Object[]> sessionsObject = (List<Object[]>) q.getResultList();

			List<Session> sessions = new ArrayList<Session>();

			if (sessionsObject.size() > 0) {
				for (Object[] sessionObject : sessionsObject) {

					Integer sessionId = (Integer) sessionObject[0];
					Date sessionStartedAt = (Date) sessionObject[1];
					Date sessionEndedAt = (Date) sessionObject[2];

					Integer sessionTypeId = (Integer) sessionObject[3];
					String sessionTypeCode = (String) sessionObject[4];
					String sessionTypeName = (String) sessionObject[5];

					Integer campaignId = (Integer) sessionObject[6];
					String campaignCode = (String) sessionObject[7];
					String campaignName = (String) sessionObject[8];

					Integer sessionAgentId = (Integer) sessionObject[9];
					String sessionAgentCode = (String) sessionObject[10];

					Session session = new Session();
					session.setId(sessionId);
					session.setStartedAt(sessionStartedAt);
					session.setEndedAt(sessionEndedAt);

					SessionType sessionType = new SessionType();
					sessionType.setId(sessionTypeId);
					sessionType.setCode(sessionTypeCode);
					sessionType.setName(sessionTypeName);
					session.setSessionType(sessionType);

					Campaign campaign = new Campaign();
					campaign.setId(campaignId);
					campaign.setCode(campaignCode);
					campaign.setName(campaignName);
					session.setCampaign(campaign);

					Agent agent = new Agent();
					agent.setId(sessionAgentId);
					agent.setCode(sessionAgentCode);
					session.setAgent(agent);

					sessions.add(session);

				}
			}

			return sessions;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	@SuppressWarnings("unchecked")
	public List<SessionDto> findSessionsForSupervisor(
			List<Integer> sessionTypesId, List<Integer> campaignsId,
			List<Integer> supervisorsId, Date startedAt,
			String code, String name, String documentNumber, String username,
			String host, String peer, Boolean openSessions) throws EaoExcepcion {
		try {

			String query =

			"select s.id,c.name,sp.firstname,sp.lastname,s.host,s.peer, "
					+ "ap.document_number,au.username,ap.firstname,ap.lastname, "
					+ "a.code,st.name,s.started_at,s.ended_at "
					+ "from ccenter.session s "
					+ "left join ccenter.agent a on a.id=s.agent_id "
					+ "left join ccenter.session_type st on st.id=s.session_type_id "
					+ "left join ccenter.campaign c on c.id=s.campaign_id "
					+ "left join security.user au on au.id=a.user_id "
					+ "left join security.person ap on ap.id=au.person_id "
					+ "left join security.user su on su.id=a.supervisor_id "
					+ "left join security.person sp on sp.id=su.person_id "
					+ "where s.id > 0 ";

			if (campaignsId != null && campaignsId.size() > 0) {
				query += " and s.campaign_id in ";
				query += " ( ";
				for (int i = 0; i < campaignsId.size(); i++) {
					Integer campaignId = campaignsId.get(i);
					if (i < campaignsId.size() - 1) {
						query += campaignId + ",";
					} else {
						query += campaignId;
					}
				}
				query += " ) ";
			}

			if (sessionTypesId != null && sessionTypesId.size() > 0) {
				query += " and s.session_type_id in ";
				query += " ( ";
				for (int i = 0; i < sessionTypesId.size(); i++) {
					Integer sessionTypeId = sessionTypesId.get(i);
					if (i < sessionTypesId.size() - 1) {
						query += sessionTypeId + ",";
					} else {
						query += sessionTypeId;
					}
				}
				query += " ) ";
			}

			if (supervisorsId != null && supervisorsId.size() > 0) {
				query += " and a.supervisor_id in ";
				query += " ( ";
				for (int i = 0; i < supervisorsId.size(); i++) {
					Integer supervisorId = supervisorsId.get(i);
					if (i < supervisorsId.size() - 1) {
						query += supervisorId + ",";
					} else {
						query += supervisorId;
					}
				}
				query += " ) ";
			}
			
			if (startedAt != null) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				query += " and s.started_at like '" + sdf.format(startedAt) + "%'";
			}

			if (code != null && !code.equals("")) {
				query += " and a.code like '%" + code + "%'";
			}

			if (username != null && !username.equals("")) {
				query += " and au.username like '%" + username + "%'";
			}

			if (documentNumber != null && !documentNumber.equals("")) {
				query += " and ap.document_number like '%" + documentNumber
						+ "%'";
			}

			if (name != null && !name.equals("")) {
				query += " and (ap.firstname like '%" + name
						+ "%' or ap.lastname like '%" + name + "%')";
			}
			
			if (host != null && !host.equals("")) {
				query += " and s.host like '%" + host + "%'";
			}
			
			if (peer != null && !peer.equals("")) {
				query += " and s.peer like '%" + peer + "%'";
			}
			
			if (openSessions != null) {
				if (openSessions.equals(Boolean.TRUE)) {
					query += " and s.ended_at is null ";	
				}else if(openSessions.equals(Boolean.FALSE)) {
					query += " and s.ended_at is not null ";	
				}
				
			}

			
			
			Query q = em.createNativeQuery(query);

			List<Object[]> sessionsObject = (List<Object[]>) q.getResultList();

			List<SessionDto> sessions = new ArrayList<SessionDto>();

			if (sessionsObject.size() > 0) {
				for (Object[] sessionObject : sessionsObject) {

					Integer sessionId = (Integer) sessionObject[0];
					String campaignName = (String) sessionObject[1];
					String sessionSupervisorFirstname = (String) sessionObject[2];
					String sessionSupervisorLastname = (String) sessionObject[3];
					String sessionHost = (String) sessionObject[4];
					String sessionPeer = (String) sessionObject[5];
					String sessionAgentDocumentNumber = (String) sessionObject[6];
					String sessionAgentUsername = (String) sessionObject[7];
					String sessionAgentFirstname = (String) sessionObject[8];
					String sessionAgentLastname = (String) sessionObject[9];
					String sessionAgentCode = (String) sessionObject[10];
					String sessionTypeName = (String) sessionObject[11];
					Date sessionStartedAt = (Date) sessionObject[12];
					Date sessionEndedAt = (Date) sessionObject[13];

					SessionDto session = new SessionDto();
					session.setId(sessionId);
					session.setHost(sessionHost);
					session.setPeer(sessionPeer);
					session.setStartedAt(sessionStartedAt);
					session.setEndedAt(sessionEndedAt);
					
					CampaignDto campaign = new CampaignDto();
					campaign.setName(campaignName);
					session.setCampaign(campaign);
					
					SupervisorDto supervisor = new SupervisorDto();
					supervisor.setFirstname(sessionSupervisorFirstname);
					supervisor.setLastname(sessionSupervisorLastname);
					
					AgentDto agent = new AgentDto();
					agent.setCode(sessionAgentCode);
					agent.setDocumentNumber(sessionAgentDocumentNumber);
					agent.setUsername(sessionAgentUsername);
					agent.setFirstname(sessionAgentFirstname);
					agent.setLastname(sessionAgentLastname);
					session.setAgent(agent);

					SessionTypeDto sessionType = new SessionTypeDto();
					sessionType.setName(sessionTypeName);
					session.setSessionType(sessionType);

					sessions.add(session);

				}
			}
			return sessions;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Session findOpenSession(Integer campaignId, Integer agentId,
			Integer sessionTypeId) throws EaoExcepcion {
		try {

			System.out.println("ingreso a findOpenSession");
			System.out.println(campaignId);
			System.out.println(agentId);
			System.out.println(sessionTypeId);
			Query q = em
					.createQuery("SELECT s.id,s.startedAt,c.name,st.code,st.name,s.peer,s.host "
							+ "FROM Session s "
							+ "left join s.agent a "
							+ "left join s.campaign c "
							+ "left join s.sessionType st "
							+ "WHERE a.id = :agentId "
							+ "and c.id = :campaignId "
							+ "and st.id = :sessionTypeId "
							+ "and s.endedAt is null "
							+ "order by s.startedAt desc");

			q.setParameter("agentId", agentId);
			q.setParameter("campaignId", campaignId);
			q.setParameter("sessionTypeId", sessionTypeId);

			System.out.println("antes de ejecutar el query");
			Object[] sessionObject = (Object[]) q.getSingleResult();
			System.out.println("despues de ejecutar el query");
			Integer id = (Integer) sessionObject[0];
			Date startedAt = (Date) sessionObject[1];
			String campaignName = (String) sessionObject[2];
			String sessionTypeCode = (String) sessionObject[3];
			String sessionTypeName = (String) sessionObject[4];
			String sessionPeer = (String) sessionObject[5];
			String sessionHost = (String) sessionObject[6];

			System.out.println("antes de crear el objeto");

			Session session = new Session();
			session.setId(id);
			session.setStartedAt(startedAt);

			System.out.println("antes de crear el objeto 2");
			Agent agent = new Agent();
			agent.setId(agentId);
			session.setAgent(agent);
			Campaign campaign = new Campaign();
			campaign.setId(campaignId);
			campaign.setName(campaignName);
			session.setCampaign(campaign);
			SessionType sessionType = new SessionType();
			sessionType.setId(sessionTypeId);
			sessionType.setCode(sessionTypeCode);
			sessionType.setName(sessionTypeName);
			session.setSessionType(sessionType);
			session.setPeer(sessionPeer);
			session.setHost(sessionHost);

			System.out.println("termino el dao");
			return session;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Session findOpenStates(Integer sessionId) throws EaoExcepcion {
		try {
			Query q = em
					.createQuery("SELECT s.id,s.startedAt,st.id,st.code,st.name,c.id,c.name,a.id,s.peer,s.host "
							+ "FROM Session s "
							+ "left join s.session sp "
							+ "left join s.campaign c "
							+ "left join s.sessionType st "
							+ "left join s.agent a "
							+ "where sp.id = :sessionId "
							+ "and s.endedAt is null "
							+ "order by s.startedAt desc");

			q.setParameter("sessionId", sessionId);

			Object[] sessionObject = (Object[]) q.getSingleResult();
			System.out.println("antes de open session1");
			Integer id = (Integer) sessionObject[0];
			Date startedAt = (Date) sessionObject[1];
			Integer sessionTypeId = (Integer) sessionObject[2];
			String sessionTypeCode = (String) sessionObject[3];
			String sessionTypeName = (String) sessionObject[4];
			Integer campaignId = (Integer) sessionObject[5];
			String campaignName = (String) sessionObject[6];
			Integer AgentId = (Integer) sessionObject[7];
			String peer = (String) sessionObject[8];
			String host = (String) sessionObject[9];

			System.out.println("despues de open session");
			Session session = new Session();
			session.setId(id);
			session.setStartedAt(startedAt);
			Campaign campaign = new Campaign();
			campaign.setId(campaignId);
			campaign.setName(campaignName);
			session.setCampaign(campaign);
			SessionType sessionType = new SessionType();
			sessionType.setId(sessionTypeId);
			sessionType.setCode(sessionTypeCode);
			sessionType.setName(sessionTypeName);
			session.setSessionType(sessionType);
			Agent agent = new Agent();
			agent.setId(AgentId);
			session.setAgent(agent);
			session.setPeer(peer);
			session.setHost(host);
			Session sessionParent = new Session();
			sessionParent.setId(sessionId);
			session.setSession(sessionParent);

			return session;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

}
